﻿using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Easy.Common;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace Easy.DAL
{
    public class FormBaseDAL
    {
        private readonly SqlDatabase sqldb = new SqlDatabase(Config.TulingBuyCrmConnectString);
        private string Entity;
        public FormBaseDAL(string entity)
        {
            this.Entity = entity;
        }
        public string getEntityName()
        {
            return sqldb.ExecuteDataSet("sp_Form_GetEntityName", new object[] { Entity }).Tables[0].Rows[0][0].ToString();
        }
        public string getRecordName(string id)
        {
            return sqldb.ExecuteDataSet("sp_Form_GetRecordName", new object[] { Entity, id }).Tables[0].Rows[0][0].ToString();
        }
        public DataTable getFormStructureSrc(string EntityName, string FormName)
        {
            string sql = "select top 1 * from [Form] where EntityName=@Entityname and FormName=@FormName ";

            DbCommand cmd = sqldb.GetSqlStringCommand(sql);
            cmd.Parameters.Add(new SqlParameter("@Entityname", EntityName));
            cmd.Parameters.Add(new SqlParameter("@FormName", FormName));
            DataTable dt = sqldb.ExecuteDataSet(cmd).Tables[0];
            return dt;
        }

        public void saveFormStructureSrc(string EntityName, string FormName, string FormHtml, string buttons, string FormJS_Src, string FormJS_beforeLoad, string FormJS_afterLoad, string FormJS_beforeSave, string FormJS_afterSave)
        {
            string sql = "update [Form] set FormHtml=@FormHtml,FormButton=@FormButton,FormJS_Src=@FormJS_Src,FormJS_beforeLoad=@FormJS_beforeLoad,FormJS_afterLoad=@FormJS_afterLoad,FormJS_beforeSave=@FormJS_beforeSave,FormJS_afterSave=@FormJS_afterSave where EntityName=@Entityname and FormName=@FormName";

            DbCommand cmd = sqldb.GetSqlStringCommand(sql);
            cmd.Parameters.Add(new SqlParameter("@Entityname", EntityName));
            cmd.Parameters.Add(new SqlParameter("@FormName", FormName));
            cmd.Parameters.Add(new SqlParameter("@FormHtml", FormHtml));
            cmd.Parameters.Add(new SqlParameter("@FormButton", buttons));
            cmd.Parameters.Add(new SqlParameter("@FormJS_Src", FormJS_Src));
            cmd.Parameters.Add(new SqlParameter("@FormJS_beforeLoad", FormJS_beforeLoad));
            cmd.Parameters.Add(new SqlParameter("@FormJS_afterLoad", FormJS_afterLoad));
            cmd.Parameters.Add(new SqlParameter("@FormJS_beforeSave", FormJS_beforeSave));
            cmd.Parameters.Add(new SqlParameter("@FormJS_afterSave", FormJS_afterSave));
            sqldb.ExecuteNonQuery(cmd);
        }
        public void CreateFormStructure(string EntityName, string FormName, string FormDisplayName, string FormHtml, string FormJS_Src, string FormJS_beforeLoad, string FormJS_afterLoad, string FormJS_beforeSave, string FormJS_afterSave, string UserId,int FormType=0)
        {
            //string sql = @"update [Form] set FormHtml=@FormHtml,FormJS_Src=@FormJS_Src,FormJS_beforeLoad=@FormJS_beforeLoad,FormJS_afterLoad=@FormJS_afterLoad,FormJS_beforeSave=@FormJS_beforeSave,FormJS_afterSave=@FormJS_afterSave where EntityName=@Entityname and FormName=@FormName";


            string sql = @"INSERT INTO [Form]
                               ([FormId]
                                ,[FormName]
                                ,[EntityName]
                                ,[CreatedOn]
                                ,[CreatedBy]
                                ,[ModifiedOn]
                                ,[ModifiedBy]
                                ,[DeletionStateCode]
                                ,[DisplayName]
                                ,[FormHtml]
                                ,[FormJS_Src]
                                ,[FormJS_beforeLoad]
                                ,[FormJS_afterLoad]
                                ,[FormJS_beforeSave]
                                ,[FormJS_afterSave]
                                ,[FormTypeFlag])
                            VALUES
                               (newid()
                                ,@FormName
                                ,@Entityname
                                ,getdate()
                                ,@user
                                ,getdate()
                                ,@user
                                ,0
                                ,@FormDisplayName
                                ,@FormHtml
                                ,@FormJS_Src
                                ,@FormJS_beforeLoad
                                ,@FormJS_afterLoad
                                ,@FormJS_beforeSave
                                ,@FormJS_afterSave
                                ,@FormType)";

            DbCommand cmd = sqldb.GetSqlStringCommand(sql);
            cmd.Parameters.Add(new SqlParameter("@Entityname", EntityName));
            cmd.Parameters.Add(new SqlParameter("@FormName", FormName));
            cmd.Parameters.Add(new SqlParameter("@FormDisplayName", FormDisplayName));
            cmd.Parameters.Add(new SqlParameter("@FormHtml", FormHtml));
            cmd.Parameters.Add(new SqlParameter("@FormJS_Src", FormJS_Src));
            cmd.Parameters.Add(new SqlParameter("@FormJS_beforeLoad", FormJS_beforeLoad));
            cmd.Parameters.Add(new SqlParameter("@FormJS_afterLoad", FormJS_afterLoad));
            cmd.Parameters.Add(new SqlParameter("@FormJS_beforeSave", FormJS_beforeSave));
            cmd.Parameters.Add(new SqlParameter("@FormJS_afterSave", FormJS_afterSave));
            cmd.Parameters.Add(new SqlParameter("@user", UserId));
            cmd.Parameters.Add(new SqlParameter("@FormType", FormType));
            sqldb.ExecuteNonQuery(cmd);
        }
    }
    public class LinkEntity
    {
        public string EntityName;
        // 0: 1:N
        // 1: N:N
        public int LinkType;
    }

    public class ReportDal {
        private readonly SqlDatabase sqldb = new SqlDatabase(Config.TulingBuyCrmConnectString);
        private string Procname;
        private string Procparam;
        public ReportDal() {
        }
        public ReportDal(string procname, string procparam) {
            this.Procname = procname;
            this.Procparam = procparam;
        }

        public string getReport(string procname, string procparam, string paramtype)
        {
            string strProcedureName = procname;

            Dictionary<string, object> listInfo = new Dictionary<string, object>();
            JArray ja = (JArray)JsonConvert.DeserializeObject(procparam);
            JArray type = (JArray)JsonConvert.DeserializeObject(paramtype);
            object[] param1 = new object[ja.Count];
            for (int i=0;i<type.Count;i++) {
                bool isnotnull = !string.IsNullOrWhiteSpace(ja[i]["param"].ToString());
                switch (type[i]["type"].ToString()) {
                    case "key":
                    case "lookup":
                        if (isnotnull)
                        {
                            param1[i] = (Object)(new Guid(ja[i]["param"].ToString()));
                        }
                        else {
                            param1[i] = (Object)null;
                        }
                        break;
                    case "picklist":
                    case "nvarchar":
                        if (isnotnull)
                        {
                            param1[i] = (Object)(ja[i]["param"].ToString());
                        }
                        else {
                            param1[i] = (Object)null;
                        }
                        break;
                    case "int":
                        if (isnotnull)
                        {
                            param1[i] = (Object)(int.Parse(ja[i]["param"].ToString()));
                        }
                        else {
                            param1[i] = (Object)null;
                        }
                        break;
                    case "float":
                        if (isnotnull)
                        {
                            param1[i] = (Object)(float.Parse(ja[i]["param"].ToString()));
                        }
                        else {
                            param1[i] = (Object)null;
                        } 
                        break;
                    case "date":
                        if (isnotnull)
                        {
                            param1[i] = (Object)(((DateTime)(ja[i]["param"])).ToString("yyyy-MM-dd"));
                        }
                        else {
                            param1[i] = (Object)null;
                        }
                        break;
                    case "datetime":
                        if (isnotnull)
                        {
                            param1[i] = (Object)(((DateTime)(ja[i]["param"])).ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                        else {
                            param1[i] = (Object)null;
                        }
                        break;

                }
            }

            DataSet ds = sqldb.ExecuteDataSet(strProcedureName, param1);
            listInfo.Add("data", ds.Tables[0]);
            listInfo.Add("title", ds.Tables[1]); 
            return JsonConvert.SerializeObject(listInfo);
        }
    }
}
